Checking the statistics on a table

Oracle's Cost Based Optimizer collects statistics on tables and indexes in order to determine the best access method when presented with many options. Usually this boils down to "to use, or not to use" an index.

There are three likely cases where statistics are the problem, and they all have the same solution:

• There are no statistics on the table or index.
• The statistics are old, and no longer representative of the table/index contents.
• The statistics were estimated, and an insufficient sample size was used.

To determine whether any of these are true, you can display the statistics using Oracle Enterprise Manager. Open up your database in the Navigator window; open the Schema folder; open the schema that owns your query table; open Tables; find your table name and open it; open Indexes. When you click on the table and each of the indexes, click the Statistics tab on the right hand pane to view the statistics. If the table is partitioned, you should do the same with each partition as well.

Alternatively, download stats.sql and run it from SQL*Plus as follows:

@stats my_table_name

Where my_table_name is the name of the table for which you want to list statistics. stats.sql will display the statistics for the table, its partitions, and its indexes.

The things you are looking for are:

• The statistics fields are NULL, indicating the statistics have not been calculated at all (or have been deleted).
• The Last Analyzed date is old, and statistics need to be recalculated. Other signs of this problem would be Total Rows showing a number that is nothing like the total number of rows in the table.
• The Sample Size is less than 20% of the total number of rows in the table. In many cases, accurate statistics can be derived from a sample size of 5% or less of the total rows, but this is not always the case.

You should see the DBA if statistics need to be recalculated.